package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

public abstract class TableAbstract<T extends RowAbstract>
{
	private Connection con = DBConnection.getInstance().getDBcon();
	protected String name = "", view = "";
	protected Class rowClass = null;

	public int insert(HashMap data)
	{
		int rc = 0;

		String query = "INSERT INTO "+name+" (";
		Iterator iter = data.keySet().iterator();
		String columns = "", values = "";
		while(iter.hasNext())
		{
			String key = (String) iter.next();
			columns += key;
			values += data.get(key);
			if(iter.hasNext()) { columns += ", "; values += "', '"; }
		}
		query += columns+") VALUES ('"+values+"')";

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
			ResultSet keySet = stmt.getGeneratedKeys();
			if(keySet.next())
			{
				rc = keySet.getInt(name+"No");
			}
			stmt.close();
			System.out.println("Success: "+name+" insert: "+query);
		}
		catch(SQLException ex)
		{
			System.out.println("Failure: "+name+" insert: "+query+" - message: "+ex.getMessage());
		}
		return rc;
	}

	public int update(HashMap data, String where, int limit)
	{
		int rc = -1;

		String query = "UPDATE "+name+" SET ";
		Iterator iter = data.keySet().iterator();
		while(iter.hasNext())
		{
			String key = (String) iter.next();
			query += key+" = '"+data.get(key)+"'";
			if(iter.hasNext()) { query += ", "; }
		}
		query += ((where != null) ? "WHERE "+where : "");
		query += ((limit != 0) ? "LIMIT "+limit : "");

		try
		{
			Statement stmt = con.createStatement();
			rc = stmt.executeUpdate(query);
			stmt.close();
			System.out.println("Success: "+name+" update: "+query);
		}
		catch(SQLException ex)
		{
			System.out.println("Failure: "+name+" update: "+query+" - message: "+ex.getMessage());
		}
		return rc;
	}

	public int delete(String where, int limit)
	{
		int rc = -1;

		String query = "DELETE FROM "+name+" ";
		query += ((where != null) ? "WHERE "+where : "");
		query += ((limit != 0) ? "LIMIT "+limit : "");
		
		try
		{
			Statement stmt = con.createStatement();
			rc = stmt.executeUpdate(query);
			stmt.close();
			System.out.println("Success: "+name+" delete: "+query);
		}
		catch (SQLException e)
		{
			System.out.println("Failure: "+name+" delete: "+query+" - message: "+e.getMessage());
		}
		return rc;
	}

	public ArrayList<T> fetchAll()
	{
		return fetchAll(null, null, 0);
	}

	public ArrayList<T> fetchAll(String where, String order, int limit)
	{
		String query = "SELECT * FROM "+view+" ";
		query += ((where != null) ? "WHERE "+where : "");
		query += ((order != null) ? "ORDER BY "+order : "");
		query += ((limit != 0) ? "LIMIT "+limit : "");

		ArrayList<T> rows = new ArrayList<T>();
		Statement stmt = null;
		ResultSet result = null;
		try
		{			
			stmt = con.createStatement();
			result = stmt.executeQuery(query);

			while(result.next())
			{
				T row = (T)this.getInstanceOfClass(rowClass);
				row.importData(result);
				rows.add(row);
			}
			System.out.println("Success: "+name+" fetchAll: "+query);
		}
		catch (SQLException e)
		{
			System.out.println("Failure: "+name+" fetchAll: "+query+" - message: "+e.getMessage());
			return null;
		}
		finally
		{
			try
			{
				result.close();
				stmt.close();
			}
			catch (SQLException e)
			{
				System.out.println("Failure: Couldn't close result or statement: "+e.getMessage());
			}
		}
		return rows;
	}

	public T fetchRow(String where, String order)
	{
		return (T)fetchAll(where, order, 1).get(0);
	}

	public T fetchLast()
	{
		return (T)fetchAll(null, null, 1).get(0);
	}

	private T getInstanceOfClass(Class<T> _class)
	{
		try
		{
			return _class.newInstance();
		}
		catch(Exception e)
		{
			System.out.println("Failure: '"+_class.getName()+"': "+e.getMessage());
		}
		return null;
	}
}